package com.car.excel;

import com.car.dao.EmployeeDao;
import com.car.dao.impl.EmployeeDaoImpl;
import com.car.utils.JDBCUtil;
import com.car.utils.NumberUtil;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.BufferedInputStream;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.HashSet;
import java.util.Set;

public class EmployeeExcel {
    /**
     * 加载Excel文件 并将数据写入Mysql中
     *
     * @param fileUrl 文件路径 如：excel/员工表.xlsx
     * @throws Exception 异常
     */
    public static void loadExcelAndWriteInMySQL(String fileUrl) throws Exception {
        InputStream inputStream = new FileInputStream(fileUrl);
        BufferedInputStream bufferedInputStream = new BufferedInputStream(inputStream);
        XSSFWorkbook workbook = new XSSFWorkbook(bufferedInputStream);
        XSSFSheet sheet = workbook.getSheet("Sheet1");
        // 定义行变量
        int rowIndex = 1;
        // 查询workId 避免重复问题
        EmployeeDao employeeDao = new EmployeeDaoImpl();
        Set<String> workIds = employeeDao.findWorKIds();
        if (workIds == null) {
            workIds = new HashSet<>();
        }
        while (true) {
            XSSFRow row = sheet.getRow(rowIndex);
            // 下一行为空 退出循环
            if (row == null) {
                break;
            }
            // 这一行第一列记录
            XSSFCell c0 = row.getCell(0);
            String username = c0.getStringCellValue();

            XSSFCell c1 = row.getCell(1);
            double age = c1.getNumericCellValue();

            XSSFCell c2 = row.getCell(2);
            String gender = c2.getStringCellValue();

            XSSFCell c4 = row.getCell(4);
            double salary = c4.getNumericCellValue();
            //判断workId是否重复
            String workId;
            while (true) {
                workId = NumberUtil.generateWorkId();
                if (!workIds.contains(workId)) {
                    workIds.add(workId);
                    break;
                }
            }
            // 定义插入语句
            String insertSQL = "insert into t_employee(name,age,gender,work_id,salary) VALUE (?,?,?,?,?)";
            JDBCUtil.update(insertSQL, username, age, gender
                    , workId, salary);
            // 跳到下一行记录
            rowIndex++;
        }
    }

}
